/**
 * 文件：GameDAOImpl.java
 * 作者：朱娜
 * 完成度：已完成
 * 最后修改日期：2023-7-4 19:00
 */

package com.gomokult.dao.impl;

import com.gomokult.dao.GameDAO;
import com.gomokult.entity.Game;
import com.gomokult.entity.User;
import com.gomokult.exception.InvalidIDException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

import static com.gomokult.utility.JDBCUtil.*;
public class GameDAOImpl implements GameDAO {
    public GameDAOImpl() {}
    private static final GameDAOImpl singleton = new GameDAOImpl();
    public static GameDAOImpl getSingleton() { return singleton; }

    @Override
    public Game getGame(int gameID) {//修改成功
        try {
            String sql = "select * from historygames where gameID = ?";
            pstat = con.prepareStatement(sql);
            pstat.setInt(1, gameID);
            ResultSet rs = pstat.executeQuery();
            rs.next();
            int userBlack = rs.getInt("playerBlack");
            int userWhite = rs.getInt("playerWhite");
            String winner = rs.getString("winner");
            String piecesSequence = rs.getString("piecesSequence");
            Timestamp date = rs.getTimestamp("date");
            int blackDuration = rs.getInt("blackDuration");
            int whiteDuration = rs.getInt("whiteDuration");
            String blackCounting = rs.getString("blackCounting");
            String whiteCounting = rs.getString("whiteCounting");
            int likesCount = rs.getInt("likesCount");
            int commentCount = rs.getInt("commentCount");
            User userb = UserDAOImpl.getSingleton().selectUserbyID(userBlack);
            User userw = UserDAOImpl.getSingleton().selectUserbyID(userWhite);
            Game game = new Game(gameID,userb,userw,winner,piecesSequence,date,blackDuration,whiteDuration,blackCounting,whiteCounting,likesCount,commentCount);

            return game;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }

    @Override
    public void updateGame(Game game) throws InvalidIDException {//修改完
        try {if(game.getGameID()==0){
            throw new InvalidIDException();
        }
            String sql = "update historygames set playerBlack=? ,playerWhite=? ,winner=? ,piecesSequence=? ,date=? ,"
            		+ "blackDuration=?, whiteDuration=? ,likesCount=? ,commentCount=? where GameID = ?";
            pstat = con.prepareStatement(sql);
            pstat.setInt(1, game.getUserBlack().getUserID());
            pstat.setInt(2, game.getUserWhite().getUserID());
            pstat.setString(3, game.getWinner());
            pstat.setString(4, game.getPiecesSequence());
            pstat.setTimestamp(5, (Timestamp) game.getDate());
            pstat.setInt(6,game.getBlackDuration());
            pstat.setInt(7, game.getWhiteDuration());
            pstat.setInt(8, game.getLikesCount());
            pstat.setInt(9, game.getCommentCount());
            pstat.setInt(10, game.getGameID());
            pstat.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public int insertGame(Game gameData){//修改完
        try {
            String sql1 = "insert into historygames(playerBlack,playerWhite,winner,piecesSequence,date," +
                    "blackDuration,whiteDuration,blackCounting,whiteCounting,likesCount,commentCount) values (?,?,?,?,?,?,?,?,?,?,?)";
            pstat = con.prepareStatement(sql1);
            pstat.setInt(1, gameData.getUserBlack().getUserID());
            pstat.setInt(2, gameData.getUserWhite().getUserID());
            pstat.setString(3, gameData.getWinner());
            pstat.setString(4, gameData.getPiecesSequence());
            pstat.setTimestamp(5, (Timestamp) gameData.getDate());
            pstat.setInt(6,gameData.getBlackDuration());
            pstat.setInt(7, gameData.getWhiteDuration());
            pstat.setString(8, gameData.getBlackCounting());
            pstat.setString(9, gameData.getWhiteCounting());
            pstat.setInt(10, gameData.getLikesCount());
            pstat.setInt(11, gameData.getCommentCount());
            pstat.executeUpdate();
            pstat.close();
            String sql2="select * from historygames where playerBlack=? and playerWhite=? and date=?";
            pstat=con.prepareStatement(sql2);
            pstat.setInt(1, gameData.getUserBlack().getUserID());
            pstat.setInt(2, gameData.getUserWhite().getUserID());
            pstat.setTimestamp(3, (Timestamp) gameData.getDate());
            ResultSet rs= pstat.executeQuery();
            if(rs.next()){
                return rs.getInt("gameID");
            }else {
                return 0;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public int getGamesCount() {//修改完
        try {
            String sql = "select * from historygames";
            pstat = con.prepareStatement(sql);
            ResultSet result = pstat.executeQuery();
            int a=0;
            while (result.next()){
                a+=1;
            }
            return a;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public int getGamesCount(int userID) throws InvalidIDException{
        try {
            if(UserDAOImpl.getSingleton().selectUserbyID(userID)==null)
                throw new InvalidIDException();
            String sql = "select * from historygames where playerBlack = ? or playerWhite = ? ";
            pstat = con.prepareStatement(sql);
            pstat.setInt(1,userID);
            pstat.setInt(2,userID);
            ResultSet result = pstat.executeQuery();
            int a=0;
            while (result.next()){
                a+=1;
            }
            return a;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public int getGamesCount(String email, Timestamp startDate, Timestamp endDate) throws InvalidIDException {
        try {
            if(UserDAOImpl.getSingleton().selectUserbyEmail(email)==null)
                throw new InvalidIDException();
            User user = UserDAOImpl.getSingleton().selectUserbyEmail(email);
            String sql = "select * from historygames where playerBlack = ? or playerWhite = ? and date between ? and ? order by date DESC";
            pstat = con.prepareStatement(sql);
            pstat.setInt(1,user.getUserID());
            pstat.setInt(2,user.getUserID());
            pstat.setTimestamp(2,startDate);
            pstat.setTimestamp(3,endDate);
            ResultSet result = pstat.executeQuery();
            int a=0;
            while (result.next()){
                a+=1;
            }
            return a;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }


    @Override
    public List<Game> getGamesFromPageDate(int start,int end, String email, Timestamp startDate, Timestamp endDate) {//修改完
        try {
            String sql;
            if(Objects.equals(email, "")) {
                sql = "select * from historygames where date between ? and ? order by date DESC limit ? offset ?";
                pstat = con.prepareStatement(sql);
                pstat.setTimestamp(1, startDate);
                pstat.setTimestamp(2, endDate);
                pstat.setInt(3, end);
                pstat.setInt(4, start);
            }else {
                User user = UserDAOImpl.getSingleton().selectUserbyEmail(email);
                if(user==null)
                    return new ArrayList<>();
                sql = "select * from historygames where playerBlack = ? or playerWhite = ? and date between ? and ? order by date DESC limit ? offset ?";
                pstat = con.prepareStatement(sql);
                pstat.setInt(1,user.getUserID());
                pstat.setInt(2,user.getUserID());
                pstat.setTimestamp(3, startDate);
                pstat.setTimestamp(4, endDate);
                pstat.setInt(5, end);
                pstat.setInt(6, start);
            }
            ResultSet rs = pstat.executeQuery();
            List<Game> list = new ArrayList<>();
            while(rs.next()){
                list.add(new Game(rs.getInt("GameID"), UserDAOImpl.getSingleton().selectUserbyID(rs.getInt("playerBlack")) ,
                        UserDAOImpl.getSingleton().selectUserbyID(rs.getInt("playerWhite")), rs.getString("winner"),
                        rs.getString("piecesSequence"),rs.getTimestamp("date"),
                        rs.getInt("blackDuration"),rs.getInt("whiteDuration"),
                        rs.getString("blackCounting"),rs.getString("whiteCounting"),
                        rs.getInt("likesCount"),rs.getInt("commentCount")));
            }
            return list;
        } catch (SQLException e) {
            System.out.println("sql");
            throw new RuntimeException(e);

        }
    }

    @Override
    public List<Game> getGamesFromPage(int start, int end, int userID) {//正确
        try {
            if(userID == 0){
                String sql1 = "select * from historygames order by date DESC limit ? offset ?";
                pstat = con.prepareStatement(sql1);
                pstat.setInt(2, start);
                pstat.setInt(1, end);
                ResultSet rs1 = pstat.executeQuery();
                List<Game> list1 = new ArrayList<>();
                while(rs1.next()){
                    list1.add(new Game(rs1.getInt("GameID"), UserDAOImpl.getSingleton().selectUserbyID(rs1.getInt("playerBlack")) ,
                            UserDAOImpl.getSingleton().selectUserbyID(rs1.getInt("playerWhite")), rs1.getString("winner"),
                            rs1.getString("piecesSequence"),rs1.getTimestamp("date"),
                            rs1.getInt("blackDuration"),rs1.getInt("whiteDuration"),
                            rs1.getString("blackCounting"),rs1.getString("whiteCounting"),
                            rs1.getInt("likesCount"),rs1.getInt("commentCount")));
                    pstat.close();
                }
                return list1;
            }else{
                String sql2 = "select * from historygames where playerBlack = ? or playerWhite = ? order by date DESC limit ? offset ?";
                pstat = con.prepareStatement(sql2);
                pstat.setInt(1, userID);
                pstat.setInt(2, userID);
                pstat.setInt(4, start);
                pstat.setInt(3, end);
                ResultSet rs2 = pstat.executeQuery();
                List<Game> list2 = new ArrayList<>();
                while(rs2.next()){
                    list2.add(new Game(rs2.getInt("GameID"),UserDAOImpl.getSingleton().selectUserbyID(rs2.getInt("playerBlack")) ,
                            UserDAOImpl.getSingleton().selectUserbyID(rs2.getInt("playerWhite")),rs2.getString("winner"),
                            rs2.getString("piecesSequence"),rs2.getTimestamp("date"),
                            rs2.getInt("Blackduration"),rs2.getInt("Whiteduration"),
                            rs2.getString("blackCounting"),rs2.getString("whiteCounting"),
                            rs2.getInt("likesCount"),rs2.getInt("commentCount")));
                }
                return list2;
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public void setCommentable(int gameID, boolean commentable) throws InvalidIDException {//没有用
        try {
            String sql1 = "select * from historygames where gameID = ?";
            pstat = con.prepareStatement(sql1);
            pstat.setInt(1, gameID);
            ResultSet rs = pstat.executeQuery();
            pstat.close();
            if(rs.next()){
                String sql2 = "update historygames set commentable = ? where gameID = ?";
                pstat = con.prepareStatement(sql2);
                pstat.setBoolean(1, commentable);
                pstat.setInt(2, gameID);
            }else {
                throw new InvalidIDException();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public boolean getCommentable(int gameID) throws InvalidIDException {//没有用
        try {
            String sql1 = "select * from historygames where gameID = ?";
            pstat = con.prepareStatement(sql1);
            pstat.setInt(1, gameID);
            ResultSet rs1 = pstat.executeQuery();
            if(rs1.next()){
                String sql2 = "select commentable from historygames where gameID = ?";
                pstat = con.prepareStatement(sql2);
                pstat.setInt(1, gameID);
                ResultSet rs2 = pstat.executeQuery();
                return rs2.getBoolean("commentable");
            }else {
                throw new InvalidIDException();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public void deleteGame(int gameID) throws InvalidIDException {//修改完
        try {
            String sql1 = "select * from historygames where gameID = ?";
            pstat = con.prepareStatement(sql1);
            pstat.setInt(1, gameID);
            ResultSet rs1 = pstat.executeQuery();
            pstat.close();
            if(rs1.next()){
                String sql2 = "delete from historygames where gameID = ?";
                pstat = con.prepareStatement(sql2);
                pstat.setInt(1, gameID);
                pstat.executeUpdate();
            }else {
                throw new InvalidIDException();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}
